package lu.med.praxiswahl.wahlumed.dao.beans;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import lu.med.praxiswahl.wahlumed.dao.beans.daointerface.OPBillsDAOInt;
import lu.med.praxiswahl.wahlumed.model.beans.CustomerTBL;
import lu.med.praxiswahl.wahlumed.model.beans.OPBillsTBL;

/**
 * Session Bean implementation class OPBillsDAO
 */
@Stateless
@Local(
{ OPBillsDAOInt.class })
public class OPBillsDAO implements OPBillsDAOInt
{

    @PersistenceContext(unitName = "gynaekomedic-ejbPU")
    private EntityManager em = null;

    /**
     * Default constructor.
     */
    public OPBillsDAO()
    {
	// TODO Auto-generated constructor stub
    }

    @Override
    public void delete(Long billnumber) throws RuntimeException
    {
	OPBillsTBL bill = em.find(OPBillsTBL.class, billnumber);

	CustomerTBL cust = bill.getCustomer();

	cust.getOpBill().remove(bill);

	em.remove(bill);
	em.flush();

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<OPBillsTBL> findAvailableActualBillsByCustomer(String matricule1)
    {
	Query query = em.createNamedQuery("findAvailableActualBillsByCustomer");
	CustomerTBL customer = em.find(CustomerTBL.class, matricule1);
	query.setParameter("matricule", customer);
	return (List<OPBillsTBL>) query.getResultList();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<OPBillsTBL> findAvailableCreatedBillsByCustomer(String matricule)
    {
	Query query = em
		.createNamedQuery("findAvailableCreatedBillsByCustomer");
	query.setParameter("matricule", matricule);
	return (List<OPBillsTBL>) query.getResultList();

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<OPBillsTBL> findAvailableBillsInPC()
    {
	Query query = em.createNamedQuery("findAvailableBillsInPC");

	return (List<OPBillsTBL>) query.getResultList();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<OPBillsTBL> findAvailablePayedBillsByCustomer(String matricule)
    {
	Query query = em.createNamedQuery("findAvailablePayedBillsByCustomer");
	CustomerTBL customer = em.find(CustomerTBL.class, matricule);
	query.setParameter("matricule", customer);
	return (List<OPBillsTBL>) query.getResultList();
    }

    @Override
    public OPBillsTBL findBillsByID(Long todo)
    {
	return (OPBillsTBL) em.find(OPBillsTBL.class, todo);
    }

   /* @Override
    public Long getLastCreatedOPBillNumber()
    {
	try
	{
	    Calendar date = new GregorianCalendar();
	    int actualYear = date.get(Calendar.YEAR);

	    Query query = em.createNamedQuery("getLastCreatedOPBillNumber");
	    query.setParameter("actualYear", actualYear);
	    Long result = (Long) query.getSingleResult();

	    return result.longValue();
	}

	catch (Exception e)
	{
	    // throw new RuntimeException("BillsTbl is empty", e);
	    return -1l;
	}
    }*/
    
    @Override
    public Long getLastCreatedOPBillNumber()
    {
	try
	{
	    Calendar c = new GregorianCalendar();
	    int actualYear = c.get(Calendar.YEAR);

	    /*
	     * Long result = (Long) em.createQuery(
	     * "select max(rechnung_nummer) from HonorarTBL bi ")
	     * .getSingleResult();
	     */

	    Long result = (Long) em
		    .createQuery(
			    "select max(rechnung_nummer) from OPBillsTBL bi where bi.rechnung_nummer like '"
				    + actualYear + "%'").getSingleResult();

	    return result.longValue();
	}
	catch (Exception e)
	{
	    return -1l;
	}
    }

    @Override
    public void persist(OPBillsTBL todo)
    {
	em.persist(todo);
	em.flush();

    }

    @SuppressWarnings("unchecked")
    @Override
    public int queryDate(String dateString, String matricule, Long billId)
    {
	List<OPBillsTBL> result = (List<OPBillsTBL>) em
		.createNativeQuery(
			"SELECT distinct(d.date_dent_heure) FROM praxis.praxis_oprechnungs_details d left join praxis.praxis_ophonorar h "
				+ "on d.RECHNUNGS_ID = h.rechnung_nummer"
				+ " where d.date_dent_heure = '"
				+ dateString
				+ "' "
				+ "and d.RECHNUNGS_ID != '"
				+ billId
				+ "' "
				+ "and h.patientin_matricule_nummer = '"
				+ matricule + "'").getResultList();

	if (result.size() != 0)
	{
	    return result.size();
	}

	else
	{
	    return 0;
	}

    }

    @Override
    public void update(OPBillsTBL todo)
    {
	em.merge(todo);
	em.flush();

    }

}
